from util.common import *
from db.db_util import dataframe_from_db

if __name__ == '__main__':

    df = dataframe_from_db("""
        SELECT
            vr.VULN_NAME,
            bi.create_data FIND_TIME,
            vr.SEVERITY,
            bi.application_name APPLY_NAME
        FROM
            tf_smp_vuln_result vr
            LEFT JOIN app_check_basic_info bi ON bi.task_id = vr.TASK_ID
    """)

    df_extracted = df[['VULN_NAME', 'APPLY_NAME', 'FIND_TIME', 'SEVERITY']]
    unique_ip_port = (df_extracted.sort_values(by='FIND_TIME', ascending=False).drop_duplicates(
        subset=['VULN_NAME', 'APPLY_NAME']))

    w_sql_list = []
    for row in unique_ip_port.itertuples():
        seep = {
            "id": "APREZX" + random_string(14),
            "name": row.VULN_NAME,
            "desc": None,
            "level": row.SEVERITY,
            "startTime": row.FIND_TIME,
            "solution": None,
            "applyName": row.APPLY_NAME,
        }
        message1 = render_template("""
                INSERT INTO tf_sa_app_result (id, vuln_name, vuln_desc, risk_level, tips, sys_facres, sys_dept,
                                              sys_fac, sys_bossres, partner, appdev_facres, appdev_bossres,
                                              appman_facres, evalua_scheme, appman_bossres, finder, find_date,
                                              find_type, sender, is_sended, send_date, change_id, close_date,
                                              reinforce_plandate, reinforcer, reinforce_date, reinforce_status,
                                              reinforce_desc, review_result, review_date, reviewer, facres, bossres,
                                              opt_id, times, vuln_analysis, evalua_result, vlun_details, solution,
                                              report_status, report_num, retreat_advice, reinforce_yx,
                                              review_proposal, report_date, remark, REVIEW_ATTACH, APPLY_NAME,
                                              APPLY_ID)
                VALUES ($id, $name, $desc,
                        $level, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                        '通过检测是否包含内网URl地址,判断是否发布包中是否包含测试数据。', NULL, '0', $startTime, '1', NULL,
                        '1', NULL, NULL, NULL, NULL, NULL, NULL, '-1', NULL, '-1', NULL, NULL, NULL, NULL, NULL, 1,
                        '该App应用中未包含测试数据信息。', '存在风险', NULL,
                        $solution, '3', NULL, NULL,
                        NULL, NULL, NULL, NULL, NULL, $applyName, NULL);
                      """, seep)

        w_sql_list.append(message1)

    write_file('target/SATP_APP导入.sql', w_sql_list)
